﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using NPOI.HSSF.UserModel;
using System.IO;
using NPOI.SS.UserModel;

namespace HHMMS.Core.Common
{
    /// <summary>
    /// 提供读取Xls文件到DataTable的方法。
    /// </summary>
    public class XlsReader
    {
        private HSSFWorkbook workbook = null;

        protected Sheet Load(Stream stream)
        {
            //根据路径通过已存在的excel来创建HSSFWorkbook，即整个excel文档     
            workbook = new HSSFWorkbook(stream);

            //获取excel的第一个sheet    
            return workbook.GetSheetAt(0);
        }

        protected Sheet Load(string fileName)
        {
            using (FileStream stream = new FileStream(fileName, FileMode.Open, FileAccess.Read))
            {
                return this.Load(stream);
            }
        }

        protected DataTable ReadXls(Sheet sheet, params string[] columnName)
        {
            DataTable table = new DataTable();

            //获取sheet的首行     
            Row headerRow = sheet.GetRow(0);

            //一行最后一个方格的编号 即总的列数     
            int cellCount = headerRow.LastCellNum;
            for (int i = headerRow.FirstCellNum; i < cellCount; i++)
            {
                DataColumn column = new DataColumn(columnName != null && columnName.Length > i ? columnName[i] : headerRow.GetCell(i).StringCellValue);
                table.Columns.Add(column);
            }

            //最后一列的标号  即总的行数     
            int rowCount = sheet.LastRowNum;
            for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
            {
                Row row = sheet.GetRow(i);
                DataRow dataRow = table.NewRow();
                for (int j = row.FirstCellNum; j < cellCount; j++)
                {
                    if (row.GetCell(j) != null)
                        dataRow[j] = row.GetCell(j).ToString();
                }
                table.Rows.Add(dataRow);
            }

            return table;
        }

        public static DataTable ReadXls(string fileName, params string[] columnName)
        {
            XlsReader reader = new XlsReader();
            Sheet sheet = reader.Load(fileName);
            return reader.ReadXls(sheet, columnName);
        }

        public static DataTable ReadXls(Stream stream, params string[] columnName)
        {
            XlsReader reader = new XlsReader();
            Sheet sheet = reader.Load(stream);
            return reader.ReadXls(sheet, columnName);
        }
    }
}
